import pickle
import shutil
import sqlite3
from configparser import ConfigParser

import openpyxl
from openpyxl.comments import Comment
from openpyxl.styles import Border, Side, Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

import tools_pmc

with open(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\line.pickle", "rb") as f:
    line_list = pickle.load(f)

from tdl import tdl
from tool_bom_cd import bom_change_details

list_tool_bom_cd = bom_change_details()
from tools_pmc import (today_str1, vlookup, lj_wlxxb_原, lj_data, lj_wlxxb, lj_info,
                       erp_fc_2, lj_mccr_yesterday, erp_fc_1, vlookup_pb, str_pb, real_yesterday)

conn = sqlite3.connect(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\IK-PMC-MC.db")
cursor = conn.cursor()


def mc(lookup_value):
    dict_return = {"未启用": "未启用（呆滞）",
                   "未启用呆滞": "未启用（呆滞）",
                   "停用": "停用（呆滞）",
                   "停用呆滞": "停用（呆滞）",
                   "正常A": "正常A",
                   "正常B": "正常B",
                   "正常A有呆滞风险": "A(有呆滞风险)",
                   "正常B有呆滞风险": "B(有呆滞风险)",
                   "正常C": "正常C",
                   "正常C有呆滞风险": "C(有呆滞风险)",
                   "仅为替代料": "仅为替代料",
                   "仅为替代料有呆滞风险": "仅为替代料",
                   "呆滞": "呆滞",
                   "有呆滞风险": "有呆滞风险",
                   }
    cursor.execute("SELECT  * FROM 物控信息1 WHERE 品号 =" + str(lookup_value))
    rows1 = cursor.fetchone()
    if rows1 is None:
        return None
    if rows1[4] is None and rows1[5] is None:
        return None
    else:
        return dict_return[(rows1[4] if rows1[4] is not None else "")+(rows1[5] if rows1[5] is not None else "")]



wb = openpyxl.Workbook()
wb.save(lj_info)

wb_wlxxb = openpyxl.load_workbook(lj_wlxxb_原)
ws_wlxxb = wb_wlxxb.worksheets[0]  # 物料信息表
wb_data = openpyxl.load_workbook(lj_data, data_only=True)
ws_data_yk = wb_data.worksheets[0]  # 盈亏数据
ws_data_pb = wb_data.worksheets[1]  # 拼版数据


ws_wlxxb.insert_rows(1)  # 第一行前增加空行
ws_wlxxb.insert_cols(4, 2)  # 导入物料状态列
ws_wlxxb.insert_cols(12, 2)  # 增加盘亏数量列、盘亏扣除计算列
ws_wlxxb.insert_cols(15, 1)  # 增减可用数量列

# 修正数据红底红字标记
font_red = Font(name="MS Sans Serif", color="FF0000", size=9)  # 字体颜色
fill = PatternFill(patternType="solid", fgColor="ffc7ce")  # 单元格填充颜色

# 插入列格式调整
font_unify = Font(name="MS Sans Serif", color="000000", size=9)  # 字体格式、颜色
side_unify = Side(style="thin", color="A0A0A0")  # 边框格式
alignment_unify = Alignment(horizontal='right', vertical='top', wrap_text=False, shrink_to_fit=False)  # 字体对齐格式

table_array_data_yk = list(ws_data_yk['A1':'B' + str(ws_data_yk.max_row)])
table_array_data_pb = list(ws_data_pb['A1':'I' + str(ws_data_pb.max_row)])


dict1 = {"11": 0,
         "12": 0,
         "136": 0,
         "n11": 0,
         "n12": 0,
         }

# 连接到SQLite数据库(物控信息存储数据库)


# 大循环修正良品数量，生成可用数量
for i in range(3, ws_wlxxb.max_row + 1):
    # 线缆库存修正
    if ws_wlxxb.cell(i, 1).value in line_list:
        ws_wlxxb.cell(i, 11).value = ws_wlxxb.cell(i, 10).value

    temp = mc(ws_wlxxb.cell(i, 1).value)
    if temp is not None:
        ws_wlxxb.cell(i, 4).value = temp

    if tools_pmc.物控信息拉取_sqlite数据库版(int(ws_wlxxb.cell(i, 1).value), cursor) is not None:
        ws_wlxxb.cell(i, 5).value = tools_pmc.物控信息拉取_sqlite数据库版(int(ws_wlxxb.cell(i, 1).value), cursor)

        # if temp[1] is not None:
        #     comment = Comment(temp[1], 'shen')
        #     comment.width = 300
        #     comment.height = 20
        #     ws_wlxxb.cell(i, 4).comment = comment

    # 拼版信息导入
    result = vlookup_pb(ws_wlxxb.cell(i, 1).value, table_array_data_pb)  # result为盘亏值
    if result is not None:
        comment = Comment(str_pb(result), 'shen')
        comment.width = 150
        comment.height = 50
        ws_wlxxb.cell(i, 1).comment = comment

    # 处理物料信息表第K、L列（盘亏数量、良品数量列）
    result = vlookup(ws_wlxxb.cell(i, 1).value, table_array_data_yk, 2)  # result为盘亏值
    ws_wlxxb.cell(i, 12).value = result
    if result is None:
        ws_wlxxb.cell(i, 13).value = ws_wlxxb.cell(i, 11).value
    else:
        ws_wlxxb.cell(i, 11).value = ws_wlxxb.cell(i, 10).value
        # 若存在盘亏数据，良品数量列引用库存数量，红底红字标记
        ws_wlxxb.cell(i, 11).fill = fill
        ws_wlxxb.cell(i, 11).font = font_red
        ws_wlxxb.cell(i, 13).value = ws_wlxxb.cell(i, 11).value + ws_wlxxb.cell(i, 12).value

    # 良品数量-盘亏列负值清零，红底红字标记
    if ws_wlxxb.cell(i, 13).value < 0 and int(ws_wlxxb.cell(i, 1).value) < 300000000000:
        ws_wlxxb.cell(i, 13).value = 0
        ws_wlxxb.cell(i, 13).fill = fill
        ws_wlxxb.cell(i, 13).font = font_red

    # 导入欠料数据，并计算最终可用数量
    ws_wlxxb.cell(i, 15).value = ws_wlxxb.cell(i, 13).value - ws_wlxxb.cell(i, 14).value

    # 调整插入列格式
    for j in range(12, 16):
        ws_wlxxb.cell(i, j).alignment = alignment_unify
        ws_wlxxb.cell(i, j).border = Border(top=side_unify, bottom=side_unify, left=side_unify, right=side_unify, )
        if ws_wlxxb.cell(i, j).font != font_red:
            ws_wlxxb.cell(i, j).font = font_unify
    ws_wlxxb.cell(i, 5).border = Border(top=side_unify, bottom=side_unify, left=side_unify, right=side_unify, )
    ws_wlxxb.cell(i, 5).alignment = Alignment(wrap_text=True)


    # 库存修正：205050010001、205050010002，205050010002优先满足配套
    if ws_wlxxb.cell(i, 1).value == "101200040011":
        dict1["11"] = ws_wlxxb.cell(i, 15).value
    if ws_wlxxb.cell(i, 1).value == "101200040012":
        dict1["12"] = ws_wlxxb.cell(i, 15).value
    if ws_wlxxb.cell(i, 1).value == "104150010136":
        dict1["136"] = ws_wlxxb.cell(i, 15).value
        # noinspection PyUnboundLocalVariableA
        dict1["n11"] = min(dict1["11"], int(dict1["136"] / 2))
        dict1["136"] = dict1["136"] - 2 * dict1["n11"]
        # noinspection PyUnboundLocalVariable
        dict1["12"] = min(int(dict1["12"]), int(dict1["136"] / 2))
    if ws_wlxxb.cell(i, 1).value == "205050010001":
        # noinspection PyUnboundLocalVariable
        ws_wlxxb.cell(i, 15).value = dict1["12"]
        print("205050010001库存：", dict1["12"])
    if ws_wlxxb.cell(i, 1).value == "205050010002":
        # noinspection PyUnboundLocalVariable
        ws_wlxxb.cell(i, 15).value = dict1["n11"]
        print("205050010002库存：", dict1["n11"])
    if ((ws_wlxxb.cell(i, 1).value == "102020020142" and ws_wlxxb.cell(i, 15).value != 0)
            or (ws_wlxxb.cell(i, 1).value == "102020020141") and ws_wlxxb.cell(i, 15).value != 0):
        print("Conphis d.o.o.付费借用镜头已退回，请联系采购处理！")
# 至此核心循环结束

# 关闭数据库连接
conn.close()

# 格式调整
ws_wlxxb.freeze_panes = 'A3'  # 冻结窗口

for i in range(3, ws_wlxxb.max_row + 1):  # 品号完整显示
    ws_wlxxb.cell(i, 1).data_type = "int"
    ws_wlxxb.cell(i, 6).data_type = "int"
    ws_wlxxb.cell(i, 1).number_format = "0"
    ws_wlxxb.cell(i, 6).number_format = "0"

    erp_fc_2(ws_wlxxb.cell(i, 4))

# region 列宽调整
list1 = [13, 26, 30, 8, 8, 13]
for i in range(1, 7):
    ws_wlxxb.column_dimensions[get_column_letter(i)].width = list1[i - 1]
for i in range(7, 35):
    ws_wlxxb.column_dimensions[get_column_letter(i)].width = 8.7
# endregion

# region 填入表头
list1 = ["B1", "C1", "L2", "M2", "N2", "O2", "D2", "E2"]
list2 = ["物料信息表" + today_str1,
         "物料信息结存时间：" + real_yesterday.strftime("%Y.%m.%d"),
         "盘亏数量",
         "良品（包材、螺丝）",
         "欠料数量",
         "可用数量",
         "物料状态",
         "历史记录"]
for i in range(0, 8):
    ws_wlxxb[list1[i]] = list2[i]
# endregion

list1 = ["L2", "M2", "N2", "O2", "E2"]
# noinspection SpellCheckingInspection
fill = PatternFill(patternType="solid", fgColor="FFFF00")
alignment = Alignment(horizontal='left', vertical='top', wrap_text=False, shrink_to_fit=False)
for i in list1:  # 新增列表头格式调整
    ws_wlxxb[i].fill = fill
    ws_wlxxb[i].alignment = alignment
    ws_wlxxb[i].border = Border(top=side_unify, bottom=side_unify, left=side_unify, right=side_unify)
for i in range(1, ws_wlxxb.max_row + 1):  # 新增列格式调整
    # noinspection PyTypeChecker
    ws_wlxxb.row_dimensions[i].height = 20
    for j in range(1, 35):
        if ws_wlxxb.cell(i, j).font != font_red:
            ws_wlxxb.cell(i, j).font = font_unify

erp_fc_1(ws_wlxxb.cell(2, 4))
range_active = "A2:Z" + str(ws_wlxxb.max_row)
ws_wlxxb.auto_filter.ref = range_active
ws_wlxxb.title = "物料信息表" + str(today_str1)

table_array_wlxxb = list(ws_wlxxb['A2':'Z' + str(ws_wlxxb.max_row)])
# 处理替代料列
dict_tdlmx_t_pro = tdl(table_array_wlxxb)
for i in range(3, (ws_wlxxb.max_row + 1)):
    for j in range(6, 9):
        ws_wlxxb.cell(i, j).value = None
    if ws_wlxxb.cell(i, 1).value in dict_tdlmx_t_pro:
        str1 = dict_tdlmx_t_pro[ws_wlxxb.cell(i, 1).value]["optimal"]
        if dict_tdlmx_t_pro[ws_wlxxb.cell(i, 1).value][str1]["status"] == "冻结":
            ws_wlxxb.cell(i, 6).value = "替代关系冻结" + str1
        else:
            ws_wlxxb.cell(i, 6).value = str1
        ws_wlxxb.cell(i, 7).value = dict_tdlmx_t_pro[ws_wlxxb.cell(i, 1).value][str1]["可用量"]
        ws_wlxxb.cell(i, 8).value = dict_tdlmx_t_pro[ws_wlxxb.cell(i, 1).value]["string"]
# 替代料特殊项处理
for i in range(3, (ws_wlxxb.max_row + 1)):
    if ws_wlxxb.cell(i, 1).value == "102030010010":
        ws_wlxxb.cell(i, 6).value = "由102030010001,102040010018,104150010036组成"
    if ws_wlxxb.cell(i, 1).value == "102030010014":
        ws_wlxxb.cell(i, 6).value = "由110010010854,102030010001,104150010036组成"
    if ws_wlxxb.cell(i, 1).value == "102030010015":
        ws_wlxxb.cell(i, 6).value = "由102040010028,102030010001,104150010036组成"
    # if ws_wlxxb.cell(i, 1).value == "101020010026":
    #     ws_wlxxb.cell(i, 7).value = "替代料需使用不同固件，通知烧录\n" + ws_wlxxb.cell(i, 7).value
    if ws_wlxxb.cell(i, 1).value == "101200010041":
        ws_wlxxb.cell(i, 8).value = "151M上除Smart相机不可替代\n" + ws_wlxxb.cell(i, 8).value
    if ws_wlxxb.cell(i, 1).value == "101010010189":
        ws_wlxxb.cell(i, 8).value = "使用老封装传感器时，bom要增加使用104160010082号热垫使用，单耗1\n" + ws_wlxxb.cell(i,
                                                                                                                     8).value
    # if ws_wlxxb.cell(i, 1).value == "101070020005":
    #     ws_wlxxb.cell(i, 7).value = "PA8KDC-80KM主料有质量风险，优先使用替代料RTL8211FI-CG\n" + ws_wlxxb.cell(i, 7).value
    if ws_wlxxb.cell(i, 1).value == "101060010024":
        ws_wlxxb.cell(i, 8).value = "使用此替代关系时，厚膜电阻-0402（101180040060）同步使用替代料（101180040003）\n" + ws_wlxxb.cell(i,
                                                                                                                     8).value
    if ws_wlxxb.cell(i, 1).value == "104040010029":
        ws_wlxxb.cell(i, 8).value = "104040010029、104060140013、104080160025、104090160032、104110020017、104160040003、需求同步使用替代料\n" + ws_wlxxb.cell(i,
                                                                                                                     8).value
    # if ws_wlxxb.cell(i, 1).value == "104080110011":
    #     ws_wlxxb.cell(i,
    #                   8).value = "使用此替代关系时，104160010005去除单耗0.02\n" + ws_wlxxb.cell(i, 8).value

# 新增生产试制欠料、改制返工欠料列
ws_wlxxb.cell(2, 33).fill = tools_pmc.fill_yellow
ws_wlxxb.cell(2, 34).fill = tools_pmc.fill_yellow
fill = PatternFill(patternType="solid", fgColor="F0FFFF")
ws_wlxxb.cell(2, 5).fill = fill

comment = Comment(
    "以下情况对替代料关系做冻结管理：\n1、替代料为老版本物料库存已消耗为0。\n2、主料供应不足临时使用替代料，主料供应正常后冻结。\n3、主料或替代料不在BOM中使用。",
    'shen')
ws_wlxxb.cell(2, 6).comment = comment

wb_wlxxb.create_sheet("BOM变更明细")
ws_wlxxb_bom_cd = wb_wlxxb.worksheets[1]
a = 2
for i in range(len(list_tool_bom_cd[0])):
    ws_wlxxb_bom_cd.cell(a, 1).value = list_tool_bom_cd[0][i]
    ws_wlxxb_bom_cd.cell(a, 2).value = vlookup(list_tool_bom_cd[0][i], table_array_wlxxb, 2)
    ws_wlxxb_bom_cd.cell(a, 3).value = vlookup(list_tool_bom_cd[0][i], table_array_wlxxb, 3)
    for j in range(len(list_tool_bom_cd[1][i][0])):
        ws_wlxxb_bom_cd.cell(a, 4).value = list_tool_bom_cd[1][i][4][j]
        ws_wlxxb_bom_cd.cell(a, 5).value = list_tool_bom_cd[1][i][0][j]
        ws_wlxxb_bom_cd.cell(a, 6).value = vlookup(list_tool_bom_cd[1][i][0][j], table_array_wlxxb, 2)
        ws_wlxxb_bom_cd.cell(a, 7).value = vlookup(list_tool_bom_cd[1][i][0][j], table_array_wlxxb, 3)
        ws_wlxxb_bom_cd.cell(a, 8).value = list_tool_bom_cd[1][i][1][j]
        ws_wlxxb_bom_cd.cell(a, 9).value = list_tool_bom_cd[1][i][2][j]
        ws_wlxxb_bom_cd.cell(a, 10).value = vlookup(list_tool_bom_cd[1][i][2][j], table_array_wlxxb, 2)
        ws_wlxxb_bom_cd.cell(a, 11).value = vlookup(list_tool_bom_cd[1][i][2][j], table_array_wlxxb, 3)
        ws_wlxxb_bom_cd.cell(a, 12).value = list_tool_bom_cd[1][i][3][j]
        a += 1

    for j in list_tool_bom_cd[2][i]:
        ws_wlxxb_bom_cd.cell(a, 1).value = j
        ws_wlxxb_bom_cd.cell(a, 2).value = vlookup(j, table_array_wlxxb, 2)
        ws_wlxxb_bom_cd.cell(a, 3).value = vlookup(j, table_array_wlxxb, 3)
        a += 1
    a += 1

list1 = ["品名", "规格", "型号", "变更类型", "变更后BOM组件品名", "组件规格", "组件型号", "组成用量",
         "变更前BOM组件品名",
         "组件规格", "组件型号", "组成用量"]
list2 = [16, 25, 25, 10, 16, 25, 25, 10, 16, 25, 25, 10]
for i in range(1, 13):
    ws_wlxxb_bom_cd.cell(1, i).value = list1[i - 1]
    ws_wlxxb_bom_cd.column_dimensions[get_column_letter(i)].width = list2[i - 1]

wb_wlxxb.save(lj_wlxxb)

lj_config = "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/Tools/PMCtools_Python_v2.0/dist/config.ini"
cfg = ConfigParser()
cfg.read(lj_config)
if not cfg.get('set', 'Test mode'):
    shutil.copy(lj_wlxxb,
                "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具")
print(today_str1 + ":wlxxb succeed!")
